/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package com.wisnu.example.service.impl;

import com.wisnu.example.entity.Ptik;
import com.wisnu.example.error.PtikException;
import com.wisnu.example.service.PtikDao;
import java.sql.Connection;
import java.sql.Date;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Wisnu Wardoyo
 */
public class PtikDaoImpl implements PtikDao {

    private Connection connection;

    private final String insertPtik = "INSERT INTO `PTIK` (NIM,NAMA,TANGGAL_LAHIR,TELEPON,EMAIL) VALUES(?,?,?,?,?)";

    private final String updatePtik = "UPDATE `PTIK` SET NIM = ?, NAMA = ?, TANGGAL_LAHIR = ?, TELEPON = ?, EMAIL = ? WHERE ID = ?";

    private final String deletePtik = "DELETE FROM `PTIK` WHERE ID = ?";

    private final String getByNim = "SELECT * FROM PTIK WHERE NIM = ?";

    private final String getByEmail = "SELECT * FROM PTIK WHERE EMAIL = ?";

    private final String selectAll = "SELECT * FROM PTIK";

    public PtikDaoImpl(Connection connection) {
        this.connection = connection;
    }

    @Override
    public void insertPtik(Ptik ptik) throws PtikException {
        PreparedStatement statement = null;
        try {

            connection.setAutoCommit(false);

            statement = connection.prepareStatement(insertPtik, Statement.RETURN_GENERATED_KEYS);
            statement.setString(1, ptik.getNim());
            statement.setString(2, ptik.getNama());
            statement.setDate(3, new Date(ptik.getTanggalLahir().getTime()));
            statement.setString(4, ptik.getTelepon());
            statement.setString(5, ptik.getEmail());

            statement.executeUpdate();

            ResultSet resultSet = statement.getGeneratedKeys();
            if (resultSet.next()) {
                ptik.setId(resultSet.getInt(1));
            }

            connection.commit();

        } catch (SQLException e) {
            try {
                connection.rollback();
            } catch (SQLException ex) {
            }
            throw new PtikException(e.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    @Override
    public void updatePtik(Ptik ptik) throws PtikException {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(updatePtik);
            statement.setString(1, ptik.getNim());
            statement.setString(2, ptik.getNama());
            statement.setDate(3, new Date(ptik.getTanggalLahir().getTime()));
            statement.setString(4, ptik.getTelepon());
            statement.setString(5, ptik.getEmail());
            statement.setInt(6, ptik.getId());
            statement.executeUpdate();

            connection.commit();

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
            }
            throw new PtikException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    @Override
    public void deletePtik(Integer id) throws PtikException {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(deletePtik);
            statement.setInt(1, id);
            statement.executeUpdate();

            connection.commit();

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
            }
            throw new PtikException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }

    }

    @Override
    public Ptik getPtikByNim(String nim) throws PtikException {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getByNim);
            statement.setString(1, nim);

            ResultSet result = statement.executeQuery();
            Ptik ptik = null;
            if (result.next()) {
                ptik = new Ptik();
                ptik.setId(result.getInt("ID"));
                ptik.setNim(result.getString("NIM"));
                ptik.setNama(result.getString("NAMA"));
                ptik.setTanggalLahir(result.getDate("TANGGAL_LAHIR"));
                ptik.setTelepon(result.getString("TELEPON"));
                ptik.setEmail(result.getString("EMAIL"));
            } else {
                throw new PtikException("Mahasiswa dengan NIM : " + nim + " tidak ditemukan");
            }
            connection.commit();
            return ptik;
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
            }
            throw new PtikException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }

    }

    @Override
    public Ptik getPtikByEmail(String email) throws PtikException {
        PreparedStatement statement = null;
        try {
            connection.setAutoCommit(false);
            statement = connection.prepareStatement(getByEmail);
            statement.setString(1, email);

            ResultSet result = statement.executeQuery();
            Ptik ptik = null;
            if (result.next()) {
                ptik = new Ptik();
                ptik.setId(result.getInt("ID"));
                ptik.setNim(result.getString("NIM"));
                ptik.setNama(result.getString("NAMA"));
                ptik.setTanggalLahir(result.getDate("TANGGAL_LAHIR"));
                ptik.setTelepon(result.getString("TELEPON"));
                ptik.setEmail(result.getString("EMAIL"));
            } else {
                throw new PtikException("Mahasiswa dengan Email : " + email + " tidak ditemukan");
            }
            connection.commit();
            return ptik;
        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
            }
            throw new PtikException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(false);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }
    }

    @Override
    public List<Ptik> selectAll() throws PtikException {
        Statement statement = null;
        List<Ptik> list = new ArrayList<>();
        try {
            connection.setAutoCommit(false);
            statement = connection.createStatement();

            ResultSet result = statement.executeQuery(selectAll);

            while (result.next()) {
                Ptik ptik = new Ptik();
                ptik.setId(result.getInt("ID"));
                ptik.setNim(result.getString("NIM"));
                ptik.setNama(result.getString("NAMA"));
                ptik.setTanggalLahir(result.getDate("TANGGAL_LAHIR"));
                ptik.setTelepon(result.getString("TELEPON"));
                ptik.setEmail(result.getString("EMAIL"));

                list.add(ptik);
            }

            connection.commit();
            return list;

        } catch (SQLException ex) {
            try {
                connection.rollback();
            } catch (SQLException ex1) {
            }
            throw new PtikException(ex.getMessage());
        } finally {
            try {
                connection.setAutoCommit(true);
            } catch (SQLException ex) {
            }
            if (statement != null) {
                try {
                    statement.close();
                } catch (SQLException ex) {
                }
            }
        }

    }

}
